package com.googlecode.garbagecan.dbtest.sqlite;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;

public class TestSqlite {

	private static String clazz = "org.sqlite.JDBC";
	//private static String url = "jdbc:sqlite:/opt/cloud/kernel/work/db/vemkd.db";
	private static String url = "jdbc:sqlite:/home/fkong/workspace/sqlitetest/vemkd.db";

	public static void main(String[] args) throws Exception {
		System.out.println("jdbc url: " + url);
		test5();
		test6();
	}

	public static void test1() throws Exception {
		String sql = "select * from host";
		Connection conn = getConnection();
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		int i = 0;
		while(rs.next()) {
			i++;
		}
		//System.out.println(i);
		conn.close();
	}
	
	public static void test2() throws Exception {
		long begin = System.currentTimeMillis();
		test1();
		long end = System.currentTimeMillis();
		printTime(Calendar.getInstance().getTime(), begin, end);
	}

	public static void test3() throws Exception {
		long begin = System.currentTimeMillis();
		for (int i = 0; i < 100; i++) {
			test1();
		}
		long end = System.currentTimeMillis();
		printTime(Calendar.getInstance().getTime(), begin, end);
	}
	
	public static void test4() throws Exception {
		for (int i = 0; i < 100; i++) {
			test2();
		}
	}
	
	public static void test5() throws Exception {
		System.out.println("----- test5 -----");
		Connection conn = getConnection();
		String sql = "select * from host";
		PreparedStatement ps = conn.prepareStatement(sql);
		for (int i = 0; i < 10; i++) {
			long begin = System.currentTimeMillis();
			ResultSet rs = ps.executeQuery();
			int count = 0;
			while(rs.next()) {
				count++;
			}
			//System.out.println(count);
			long end = System.currentTimeMillis();
			printTime(Calendar.getInstance().getTime(), begin, end);
			// Thread.sleep(1000);
		}
		conn.close();
	}

	public static void test6() throws Exception {
		System.out.println("----- test6 -----");
		Connection conn = getConnection();
		String sql = "select * from host where cluster='PERF_NODE_ib07b05'";
		PreparedStatement ps = conn.prepareStatement(sql);
		for (int i = 0; i < 10; i++) {
			long begin = System.currentTimeMillis();
			for(int j = 0; j < 10; j++) {
				ResultSet rs = ps.executeQuery();
				int count = 0;
				while(rs.next()) {
					count++;
				}
				//System.out.println(count);
			}
			long end = System.currentTimeMillis();
			printTime(Calendar.getInstance().getTime(), begin, end);
		}
		conn.close();
	}

	public static Connection getConnection() throws Exception {
		Class.forName(clazz);
		Connection conn = DriverManager.getConnection(url, "", "");
		return conn;
	}

	public static Date parseToDate(String s) throws ParseException {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
		Date date = df.parse(s);
		return date;
	}
	
	public static String formatToString(Date date) throws ParseException {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
		return df.format(date);
	}

	public static void printTime(Date date, long begin, long end) throws ParseException {
		StringBuffer sb = new StringBuffer();
		sb.append("[").append(formatToString(Calendar.getInstance().getTime())).append("]");
		sb.append(" ").append(end - begin).append("ms");
		System.out.println(sb.toString());
	}
}
